""""
Case Type   : D库常用语法兼容-常用函数兼容4
Case Name   : 非D库带shark插件执行合理报错
Create At   : 2025-9-25
Owner       : hejiuhuan
Description :
    1.创建数据库
    2.切换到A库，执行各个函数
    3.切换到B库，执行各个函数
    4.切换到C库，执行各个函数
    5.切换到D库，执行各个函数
    6.切换到PG库，执行各个函数
    7.清理环境
Expect      :
    1.成功
    2.cast成功其余均失败报错
    3.cast/log10成功其余均失败报错
    4.cast成功其余均失败报错
    5.cast成功其余均失败报错
    6.cast成功其余均失败报错
    7.成功
History     :
    1、2025-09-25 创建
"""
import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from yat.test import Node
from yat.test import macro
log = Logger()
primary_sh = CommonSH('PrimaryDbUser')

class Opengauss_Compatibility_Function_D_COMPATIBILITY_Case0011(unittest.TestCase):
    def setUp(self):
        log.info(f'--------{os.path.basename(__file__)} start--------')
        self.userNode = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_a = 'db_a'
        self.db_b = 'db_b'
        self.db_c = 'db_c'
        self.db_d = 'db_d'
        self.db_pg = 'db_pg'
        self.dbname = 'postgres'
    
    def test_Opengauss_Compatibility_Function_D_COMPATIBILITY_Case0011(self):
        text = '------------------step1: 创建; expect: 成功------------------'
        log.info(text)
        msg = primary_sh.check_connection_status(self.constant.START_STATUS_MSG)
        self.assertTrue(msg)
        create_db_a = f'drop database if exists {self.db_a};create database {self.db_a} dbcompatibility \'A\';'
        msg_create_db_a = primary_sh.execut_db_sql(create_db_a, dbname=self.dbname)
        log.info(msg_create_db_a)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg_create_db_a, '创建A库失败')
        create_db_b = f'drop database if exists {self.db_b};create database {self.db_b} dbcompatibility \'B\';'
        msg_create_db_b = primary_sh.execut_db_sql(create_db_b, dbname=self.dbname)
        log.info(msg_create_db_b)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg_create_db_b, '创建B库失败')
        create_db_c = f'drop database if exists {self.db_c};create database {self.db_c} dbcompatibility \'C\';'
        msg_create_db_c = primary_sh.execut_db_sql(create_db_c, dbname=self.dbname)
        log.info(msg_create_db_c)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg_create_db_c, '创建C库失败')
        create_db_d = f'drop database if exists {self.db_d};create database {self.db_d} dbcompatibility \'D\';'
        msg_create_db_d = primary_sh.execut_db_sql(create_db_d, dbname=self.dbname)
        log.info(msg_create_db_d)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg_create_db_d, '创建D库失败')
        create_db_pg = f'drop database if exists {self.db_pg};create database {self.db_pg} dbcompatibility \'PG\';'
        msg_create_db_pg = primary_sh.execut_db_sql(create_db_pg, dbname=self.dbname)
        log.info(msg_create_db_pg)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg_create_db_pg, '创建PG库失败')

        text = '------------------step2: 切换到A库，执行各个函数; expect: 成功------------------'
        log.info(text)
        isnull = 'select isnull(1,2);'
        msg_isnull = primary_sh.execut_db_sql(isnull, dbname=self.db_a)
        log.info(msg_isnull)
        self.assertIn('ERROR:  function isnull(integer, integer) does not exist', msg_isnull)
        atn2 = 'select atn2(1,2);'
        msg_atn2 = primary_sh.execut_db_sql(atn2, dbname=self.db_a)
        log.info(msg_atn2)
        self.assertIn('ERROR:  function atn2(integer, integer) does not exist', msg_atn2)
        log10 = 'select log10(1);'
        msg_log10 = primary_sh.execut_db_sql(log10, dbname=self.db_a)
        log.info(msg_log10)
        self.assertIn('ERROR:  function log10(integer) does not exist', msg_log10)
        datediff = 'select datediff(year,\'2025-01-01\',\'2026-01-02\');'
        msg_datediff = primary_sh.execut_db_sql(datediff, dbname=self.db_a)
        log.info(msg_datediff)
        self.assertIn('ERROR:  column "year" does not exist', msg_datediff)
        datediff_big = 'select datediff_big(year,\'2025-01-01\',\'2026-01-02\');'
        msg_datediff_big = primary_sh.execut_db_sql(datediff_big, dbname=self.db_a)
        log.info(msg_datediff_big)
        self.assertIn('ERROR:  column "year" does not exist', msg_datediff_big)
        charindex = 'select charindex(\'1\',\'abc123xyz\');'
        msg_charindex = primary_sh.execut_db_sql(charindex, dbname=self.db_a)
        log.info(msg_charindex)
        self.assertIn('ERROR:  function charindex(unknown, unknown) does not exist', msg_charindex)
        cast = 'select cast(9/2 as char(1));'
        msg_cast = primary_sh.execut_db_sql(cast, dbname=self.db_a)
        log.info(msg_cast)
        self.assertIn('4', msg_cast)
        try_cast = 'select try_cast(\'1.123456789\' as numeric(10,5));'
        msg_try_cast = primary_sh.execut_db_sql(try_cast, dbname=self.db_a)
        log.info(msg_try_cast)
        self.assertIn('ERROR:  syntax error at or near "as"', msg_try_cast)
        convert = 'select convert(varchar,123456);'
        msg_convert = primary_sh.execut_db_sql(convert, dbname=self.db_a)
        log.info(msg_convert)
        self.assertIn('ERROR:  column "varchar" does not exist', msg_convert)
        try_convert = 'select try_convert(varchar,123456);'
        msg_try_convert = primary_sh.execut_db_sql(try_convert, dbname=self.db_a)
        log.info(msg_try_convert)
        self.assertIn('ERROR:  column "varchar" does not exist', msg_try_convert)

        text = '------------------step3: 切换到B库，执行各个函数; expect: 成功------------------'
        log.info(text)
        isnull = 'select isnull(1,2);'
        msg_isnull = primary_sh.execut_db_sql(isnull, dbname=self.db_b)
        log.info(msg_isnull)
        self.assertIn('ERROR:  syntax error at or near ","', msg_isnull)
        atn2 = 'select atn2(1,2);'
        msg_atn2 = primary_sh.execut_db_sql(atn2, dbname=self.db_b)
        log.info(msg_atn2)
        self.assertIn('ERROR:  function atn2(integer, integer) does not exist', msg_atn2)
        log10 = 'select log10(1);'
        msg_log10 = primary_sh.execut_db_sql(log10, dbname=self.db_b)
        log.info(msg_log10)
        self.assertIn('0', msg_log10)
        datediff = 'select datediff(year,\'2025-01-01\',\'2026-01-02\');'
        msg_datediff = primary_sh.execut_db_sql(datediff, dbname=self.db_b)
        log.info(msg_datediff)
        self.assertIn('ERROR:  column "year" does not exist', msg_datediff)
        datediff_big = 'select datediff_big(year,\'2025-01-01\',\'2026-01-02\');'
        msg_datediff_big = primary_sh.execut_db_sql(datediff_big, dbname=self.db_b)
        log.info(msg_datediff_big)
        self.assertIn('ERROR:  column "year" does not exist', msg_datediff_big)
        charindex = 'select charindex(\'1\',\'abc123xyz\');'
        msg_charindex = primary_sh.execut_db_sql(charindex, dbname=self.db_b)
        log.info(msg_charindex)
        self.assertIn('ERROR:  function charindex(unknown, unknown) does not exist', msg_charindex)
        cast = 'select cast(9/2 as char(1));'
        msg_cast = primary_sh.execut_db_sql(cast, dbname=self.db_b)
        log.info(msg_cast)
        self.assertIn('4', msg_cast)
        try_cast = 'select try_cast(\'1.123456789\' as numeric(10,5));'
        msg_try_cast = primary_sh.execut_db_sql(try_cast, dbname=self.db_b)
        log.info(msg_try_cast)
        self.assertIn('ERROR:  syntax error at or near "as"', msg_try_cast)
        convert = 'select convert(varchar,123456);'
        msg_convert = primary_sh.execut_db_sql(convert, dbname=self.db_b)
        log.info(msg_convert)
        self.assertIn('ERROR:  syntax error at or near "123456"', msg_convert)
        try_convert = 'select try_convert(varchar,123456);'
        msg_try_convert = primary_sh.execut_db_sql(try_convert, dbname=self.db_b)
        log.info(msg_try_convert)
        self.assertIn('ERROR:  column "varchar" does not exist', msg_try_convert)

        text = '------------------step4: 切换到C库，执行各个函数; expect: 成功------------------'
        log.info(text)
        isnull = 'select isnull(1,2);'
        msg_isnull = primary_sh.execut_db_sql(isnull, dbname=self.db_c)
        log.info(msg_isnull)
        self.assertIn('ERROR:  function isnull(integer, integer) does not exist', msg_isnull)
        atn2 = 'select atn2(1,2);'
        msg_atn2 = primary_sh.execut_db_sql(atn2, dbname=self.db_c)
        log.info(msg_atn2)
        self.assertIn('ERROR:  function atn2(integer, integer) does not exist', msg_atn2)
        log10 = 'select log10(1);'
        msg_log10 = primary_sh.execut_db_sql(log10, dbname=self.db_c)
        log.info(msg_log10)
        self.assertIn('ERROR:  function log10(integer) does not exist', msg_log10)
        datediff = 'select datediff(year,\'2025-01-01\',\'2026-01-02\');'
        msg_datediff = primary_sh.execut_db_sql(datediff, dbname=self.db_c)
        log.info(msg_datediff)
        self.assertIn('ERROR:  column "year" does not exist', msg_datediff)
        datediff_big = 'select datediff_big(year,\'2025-01-01\',\'2026-01-02\');'
        msg_datediff_big = primary_sh.execut_db_sql(datediff_big, dbname=self.db_c)
        log.info(msg_datediff_big)
        self.assertIn('ERROR:  column "year" does not exist', msg_datediff_big)
        charindex = 'select charindex(\'1\',\'abc123xyz\');'
        msg_charindex = primary_sh.execut_db_sql(charindex, dbname=self.db_c)
        log.info(msg_charindex)
        self.assertIn('ERROR:  function charindex(unknown, unknown) does not exist', msg_charindex)
        cast = 'select cast(9/2 as char(1));'
        msg_cast = primary_sh.execut_db_sql(cast, dbname=self.db_c)
        log.info(msg_cast)
        self.assertIn('4', msg_cast)
        try_cast = 'select try_cast(\'1.123456789\' as numeric(10,5));'
        msg_try_cast = primary_sh.execut_db_sql(try_cast, dbname=self.db_c)
        log.info(msg_try_cast)
        self.assertIn('ERROR:  syntax error at or near "as"', msg_try_cast)
        convert = 'select convert(varchar,123456);'
        msg_convert = primary_sh.execut_db_sql(convert, dbname=self.db_c)
        log.info(msg_convert)
        self.assertIn('ERROR:  column "varchar" does not exist', msg_convert)
        try_convert = 'select try_convert(varchar,123456);'
        msg_try_convert = primary_sh.execut_db_sql(try_convert, dbname=self.db_c)
        log.info(msg_try_convert)
        self.assertIn('ERROR:  column "varchar" does not exist', msg_try_convert)

        text = '------------------step5: 切换到D库，执行各个函数; expect: 成功------------------'
        log.info(text)
        isnull = 'select isnull(1,2);'
        msg_isnull = primary_sh.execut_db_sql(isnull, dbname=self.db_d)
        log.info(msg_isnull)
        self.assertIn('ERROR:  function isnull(integer, integer) does not exist', msg_isnull)
        atn2 = 'select atn2(1,2);'
        msg_atn2 = primary_sh.execut_db_sql(atn2, dbname=self.db_d)
        log.info(msg_atn2)
        self.assertIn('ERROR:  function atn2(integer, integer) does not exist', msg_atn2)
        log10 = 'select log10(1);'
        msg_log10 = primary_sh.execut_db_sql(log10, dbname=self.db_d)
        log.info(msg_log10)
        self.assertIn('ERROR:  function log10(integer) does not exist', msg_log10)
        datediff = 'select datediff(year,\'2025-01-01\',\'2026-01-02\');'
        msg_datediff = primary_sh.execut_db_sql(datediff, dbname=self.db_d)
        log.info(msg_datediff)
        self.assertIn('ERROR:  schema "sys" does not exist', msg_datediff)
        datediff_big = 'select datediff_big(year,\'2025-01-01\',\'2026-01-02\');'
        msg_datediff_big = primary_sh.execut_db_sql(datediff_big, dbname=self.db_d)
        log.info(msg_datediff_big)
        self.assertIn('ERROR:  schema "sys" does not exist', msg_datediff_big)
        charindex = 'select charindex(\'1\',\'abc123xyz\');'
        msg_charindex = primary_sh.execut_db_sql(charindex, dbname=self.db_d)
        log.info(msg_charindex)
        self.assertIn('ERROR:  function charindex(unknown, unknown) does not exist', msg_charindex)
        cast = 'select cast(9/2 as char(1));'
        msg_cast = primary_sh.execut_db_sql(cast, dbname=self.db_d)
        log.info(msg_cast)
        self.assertIn('4', msg_cast)
        try_cast = 'select try_cast(\'1.123456789\' as numeric(10,5));'
        msg_try_cast = primary_sh.execut_db_sql(try_cast, dbname=self.db_d)
        log.info(msg_try_cast)
        self.assertIn('ERROR:  schema "sys" does not exist', msg_try_cast)
        convert = 'select convert(varchar,123456);'
        msg_convert = primary_sh.execut_db_sql(convert, dbname=self.db_d)
        log.info(msg_convert)
        self.assertIn('ERROR:  schema "sys" does not exist', msg_convert)
        try_convert = 'select try_convert(varchar,123456);'
        msg_try_convert = primary_sh.execut_db_sql(try_convert, dbname=self.db_d)
        log.info(msg_try_convert)
        self.assertIn('ERROR:  schema "sys" does not exist', msg_try_convert)

        text = '------------------step6: 切换到PG库，执行各个函数; expect: 成功------------------'
        log.info(text)
        isnull = 'select isnull(1,2);'
        msg_isnull = primary_sh.execut_db_sql(isnull, dbname=self.db_pg)
        log.info(msg_isnull)
        self.assertIn('ERROR:  function isnull(integer, integer) does not exist', msg_isnull)
        atn2 = 'select atn2(1,2);'
        msg_atn2 = primary_sh.execut_db_sql(atn2, dbname=self.db_pg)
        log.info(msg_atn2)
        self.assertIn('ERROR:  function atn2(integer, integer) does not exist', msg_atn2)
        log10 = 'select log10(1);'
        msg_log10 = primary_sh.execut_db_sql(log10, dbname=self.db_pg)
        log.info(msg_log10)
        self.assertIn('ERROR:  function log10(integer) does not exist', msg_log10)
        datediff = 'select datediff(year,\'2025-01-01\',\'2026-01-02\');'
        msg_datediff = primary_sh.execut_db_sql(datediff, dbname=self.db_pg)
        log.info(msg_datediff)
        self.assertIn('ERROR:  column "year" does not exist', msg_datediff)
        datediff_big = 'select datediff_big(year,\'2025-01-01\',\'2026-01-02\');'
        msg_datediff_big = primary_sh.execut_db_sql(datediff_big, dbname=self.db_pg)
        log.info(msg_datediff_big)
        self.assertIn('ERROR:  column "year" does not exist', msg_datediff_big)
        charindex = 'select charindex(\'1\',\'abc123xyz\');'
        msg_charindex = primary_sh.execut_db_sql(charindex, dbname=self.db_pg)
        log.info(msg_charindex)
        self.assertIn('ERROR:  function charindex(unknown, unknown) does not exist', msg_charindex)
        cast = 'select cast(9/2 as char(1));'
        msg_cast = primary_sh.execut_db_sql(cast, dbname=self.db_pg)
        log.info(msg_cast)
        self.assertIn('4', msg_cast)
        try_cast = 'select try_cast(\'1.123456789\' as numeric(10,5));'
        msg_try_cast = primary_sh.execut_db_sql(try_cast, dbname=self.db_pg)
        log.info(msg_try_cast)
        self.assertIn('ERROR:  syntax error at or near "as"', msg_try_cast)
        convert = 'select convert(varchar,123456);'
        msg_convert = primary_sh.execut_db_sql(convert, dbname=self.db_pg)
        log.info(msg_convert)
        self.assertIn('ERROR:  column "varchar" does not exist', msg_convert)
        try_convert = 'select try_convert(varchar,123456);'
        msg_try_convert = primary_sh.execut_db_sql(try_convert, dbname=self.db_pg)
        log.info(msg_try_convert)
        self.assertIn('ERROR:  column "varchar" does not exist', msg_try_convert)

    def tearDown(self):
        text = '------------------step7: 清理环境; expect: 成功------------------'
        log.info(text)
        drop_db_a = f'drop database if exists {self.db_a};'
        msg_drop_db_a = primary_sh.execut_db_sql(drop_db_a, dbname=self.dbname)
        log.info(msg_drop_db_a)
        self.assertIn('DROP DATABASE', msg_drop_db_a, '删除A库失败')
        drop_db_b = f'drop database if exists {self.db_b};'
        msg_drop_db_b = primary_sh.execut_db_sql(drop_db_b, dbname=self.dbname)
        log.info(msg_drop_db_b)
        self.assertIn('DROP DATABASE', msg_drop_db_b, '删除B库失败')
        drop_db_c = f'drop database if exists {self.db_c};'
        msg_drop_db_c = primary_sh.execut_db_sql(drop_db_c, dbname=self.dbname)
        log.info(msg_drop_db_c)
        self.assertIn('DROP DATABASE', msg_drop_db_c, '删除C库失败')
        drop_db_d = f'drop database if exists {self.db_d};'
        msg_drop_db_d = primary_sh.execut_db_sql(drop_db_d, dbname=self.dbname)
        log.info(msg_drop_db_d)
        self.assertIn('DROP DATABASE', msg_drop_db_d, '删除D库失败')
        drop_db_pg = f'drop database if exists {self.db_pg};'
        msg_drop_db_pg = primary_sh.execut_db_sql(drop_db_pg, dbname=self.dbname)
        log.info(msg_drop_db_pg)
        self.assertIn('DROP DATABASE', msg_drop_db_pg, '删除PG库失败')
        log.info(f'--------{os.path.basename(__file__)} end--------')